﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.Serialization.Json;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Web.Script.Serialization;
using System.Web.Security;
using System.Xml;
using BoldSeas.Framework.Log;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;

namespace FCA.Common
{
    public static class BoldSeasToolHelper
    {
        #region Json序列化,用于发送到客户端
        /// <summary>
        /// Json序列化,用于发送到客户端
        /// </summary>
        public static string ConvertToJson(this object item)
        {
            //DataContractJsonSerializer serializer = new DataContractJsonSerializer(item.GetType());
            //using (MemoryStream ms = new MemoryStream())
            //{
            //    serializer.WriteObject(ms, item);
            //    return Encoding.UTF8.GetString(ms.ToArray());
            //}
            JavaScriptSerializer mySerializer = new JavaScriptSerializer();
            return mySerializer.Serialize(item);
        }
        #endregion

        #region Json反序列化,用于接收客户端Json后生成对应的对象
        /// <summary>
        /// Json反序列化,用于接收客户端Json后生成对应的对象
        /// </summary>
        public static T FromJsonTo<T>(this string jsonString)
        {
            //DataContractJsonSerializer ser = new DataContractJsonSerializer(typeof(T));
            //using (MemoryStream ms = new MemoryStream(Encoding.UTF8.GetBytes(jsonString)))
            //{
            //    T jsonObject = (T)ser.ReadObject(ms);
            //    return jsonObject;
            //}
            JavaScriptSerializer mySerializer = new JavaScriptSerializer();
            return mySerializer.Deserialize<T>(jsonString);

        }
        #endregion

        #region Json转换为Datatable
        /// <summary>
        /// Json转换为Datatable
        /// </summary>
        /// <param name="strJson"></param>
        /// <returns></returns>
        public static DataTable JsonToDataTable(string strJson)
        {
            //转换json格式
            strJson = strJson.Replace(",\"", "*\"").Replace("\":", "\"#").ToString();
            //取出表名   
            var rg = new Regex(@"(?<={)[^:]+(?=:\[)", RegexOptions.IgnoreCase);
            string strName = rg.Match(strJson).Value;
            DataTable tb = null;
            //去除表名   
            strJson = strJson.Substring(strJson.IndexOf("[") + 1);
            //strJson = strJson.Substring(0, strJson.IndexOf("]"));

            //获取数据   
            rg = new Regex(@"(?<={)[^}]+(?=})");
            MatchCollection mc = rg.Matches(strJson);
            for (int i = 0; i < mc.Count; i++)
            {
                string strRow = mc[i].Value;
                string[] strRows = strRow.Split('*');

                //创建表   
                if (tb == null)
                {
                    tb = new DataTable();
                    tb.TableName = strName;
                    foreach (string str in strRows)
                    {
                        var dc = new DataColumn();
                        string[] strCell = str.Split('#');

                        if (strCell[0].Substring(0, 1) == "\"")
                        {
                            int a = strCell[0].Length;
                            dc.ColumnName = strCell[0].Substring(1, a - 2);
                        }
                        else
                        {
                            dc.ColumnName = strCell[0];
                        }
                        tb.Columns.Add(dc);
                    }
                    tb.AcceptChanges();
                }

                //增加内容   
                DataRow dr = tb.NewRow();
                for (int r = 0; r < strRows.Length; r++)
                {
                    dr[r] = strRows[r].Split('#')[1].Trim().Replace("，", ",").Replace("：", ":").Replace("\"", "");
                }
                tb.Rows.Add(dr);
                tb.AcceptChanges();
            }

            return tb;
        }
        #endregion

        #region Json转换成Dictionary<string,object>
        /// <summary>
        /// Json转换成Dictionary<string,object>
        /// </summary>
        /// <param name="jsonData"></param>
        /// <returns></returns>
        public static Dictionary<string, object> JsonToDictionary(string jsonData)
        {
            //实例化JavaScriptSerializer类的新实例
            JavaScriptSerializer jss = new JavaScriptSerializer();
            try
            {
                //将指定的 JSON 字符串转换为 Dictionary<string, object> 类型的对象
                return jss.Deserialize<Dictionary<string, object>>(jsonData);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 将Json序列化的时间由/Date(1304931520336+0800)/转为（yyyy-MM-DD HH:mm-ss）字符串
        /// <summary>
        /// 将Json序列化的时间由/Date(1304931520336+0800)/转为（yyyy-MM-DD HH:mm-ss）字符串
        /// </summary>
        /// <param name="jsonDate">Json格式的时间</param>
        /// <returns></returns>
        public static string ConvertJsonDateToDateString(string jsonDate)
        {
            string result = string.Empty;
            DateTime dt = new DateTime(1970, 1, 1);
            dt = dt.AddMilliseconds(long.Parse(jsonDate));
            dt = dt.ToLocalTime();
            result = dt.ToString("yyyy-MM-dd HH:mm:ss");
            return result;
        }
        #endregion

        #region 将时间字符串转为Json时间
        /// <summary>
        /// 将时间字符串转为Json时间
        /// </summary>
        /// <param name="time">时间</param>
        /// <returns></returns>
        public static string ConvertDateStringToJsonDate(DateTime time)
        {
            TimeSpan ts = time - DateTime.Parse("1970-01-01");
            string values = ts.TotalMilliseconds.ToString();
            if (values.Contains('.'))
            {
                values = values.Split('.')[0];
            }
            return values;
        }
        #endregion

        #region 将DataTable转换成Json
        /// <summary>
        /// 将DataTable转换成Json
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string DataTableToJson(this DataTable dt)
        {

            JavaScriptSerializer jss = new JavaScriptSerializer();
            System.Collections.ArrayList dic = new System.Collections.ArrayList();
            foreach (DataRow dr in dt.Rows)
            {
                System.Collections.Generic.Dictionary<string, object> drow = new System.Collections.Generic.Dictionary<string, object>();
                foreach (DataColumn dc in dt.Columns)
                {
                    drow.Add(dc.ColumnName, dr[dc.ColumnName]);
                }
                dic.Add(drow);
            }
            //序列化  
            string result = jss.Serialize(dic);
            return result.Substring(1, result.Length - 2);
        }
        #endregion

        #region 将DataTable转换成XML
        /// <summary>
        /// 将DataTable转换成XML
        /// </summary>
        /// <param name="xmlDS"></param>
        /// <returns></returns>
        public static string ConvertDataTableToXML(DataTable xmlDS)
        {
            MemoryStream stream = null;
            XmlTextWriter writer = null;
            try
            {
                stream = new MemoryStream();
                writer = new XmlTextWriter(stream, Encoding.Default);
                xmlDS.WriteXml(writer);
                int count = (int)stream.Length;
                byte[] arr = new byte[count];
                stream.Seek(0, SeekOrigin.Begin);
                stream.Read(arr, 0, count);
                UTF8Encoding utf = new UTF8Encoding();
                return utf.GetString(arr).Trim();
            }
            catch
            {
                return String.Empty;
            }
            finally
            {
                if (writer != null) writer.Close();
            }
        }
        #endregion

        #region 将XML转换成DataTable
        /// <summary>
        /// 将XML转换成DataTable
        /// </summary>
        /// <param name="xmlUrl">XML的URL（绝对路径）</param>
        /// <returns></returns>
        public static DataTable ConvertXMLToDataTable(string xmlUrl)
        {
            XmlTextReader reader = null;
            try
            {
                DataSet xmlDS = new DataSet();
                reader = new XmlTextReader(xmlUrl);
                xmlDS.ReadXml(reader);
                return xmlDS.Tables[0];
            }
            catch (Exception ex)
            {
                string strTest = ex.Message;
                return null;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
        }
        #endregion

        #region 将Xml内容字符串转换成DataSet对象
        /// <summary>
        /// 将Xml内容字符串转换成DataSet对象
        /// </summary>
        /// <param name="xmlStr"></param>
        /// <returns></returns>
        public static DataSet CXmlToDataSet(string xmlStr)
        {
            if (!string.IsNullOrEmpty(xmlStr))
            {
                StringReader StrStream = null;
                XmlTextReader Xmlrdr = null;
                try
                {
                    DataSet ds = new DataSet();
                    //读取字符串中的信息
                    StrStream = new StringReader(xmlStr);
                    //获取StrStream中的数据
                    Xmlrdr = new XmlTextReader(StrStream);
                    //ds获取Xmlrdr中的数据                
                    ds.ReadXml(Xmlrdr);
                    return ds;
                }
                catch (Exception e)
                {
                    throw e;
                }
                finally
                {
                    //释放资源
                    if (Xmlrdr != null)
                    {
                        Xmlrdr.Close();
                        StrStream.Close();
                        StrStream.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 返回10位的时间戳
        /// <summary>
        /// 返回10位的时间戳
        /// </summary>
        /// <returns></returns>
        public static string GetTimeStamp()
        {
            TimeSpan ts = DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0, 0);
            return Convert.ToInt64(ts.TotalSeconds).ToString();
        }
        #endregion

        #region 横向DataTable取值
        /// <summary>
        /// 横向DataTable取值 
        /// </summary>
        /// <param name="table"></param>
        /// <param name="ColumnNum">第几列</param>
        /// <param name="rowValue">该列中的值</param>
        /// <returns></returns>
        public static DataRow GetDataTableValue(DataTable table, int ColumnNum, string rowValue)
        {
            if (table == null || table.Rows.Count <= 0)
            {
                return null;
            }
            string columnName = table.Columns[ColumnNum].ToString();
            DataRow[] RowArray = table.Select(columnName + "='" + rowValue + "'");
            if (RowArray.Count() > 0)
            {
                return RowArray[0];
            }
            else
            {
                return null;
            }

        }
        #endregion

        #region  FCA解析json去处单部分的值
        /// <summary>
        /// FCA解析json去处单部分的值
        /// </summary>
        /// <param name="JSON">json数据</param>
        /// <param name="MethodName">方法名</param>
        /// <param name="Token">标示</param>
        /// <returns></returns> 
        public static string ResolveJson(string JSON, ref string MethodName, ref string Token, ref string SlaveParam)
        {
            try
            {
                Dictionary<string, object> Dicjson = null; ;
                JavaScriptSerializer serializer = new JavaScriptSerializer();
                Dicjson = (Dictionary<string, object>)serializer.DeserializeObject(JSON);
                if (Dicjson != null)
                {
                    object Param, methodname, token, slaveParam = null;
                    Dicjson.TryGetValue("MethodName", out methodname);
                    Dicjson.TryGetValue("SlaveParam", out slaveParam);
                    Dicjson.TryGetValue("Token", out token);
                    MethodName = methodname.ToString();
                    Token = token.ToString();
                    if (slaveParam != null)
                    {
                        SlaveParam = serializer.Serialize(slaveParam);
                    }
                    else
                    {
                        SlaveParam = null;
                    }
                    Dicjson.TryGetValue("Param", out Param);
                    JavaScriptSerializer java = new JavaScriptSerializer();
                    string javajson = java.Serialize(Param);
                    //javajson = javajson.Replace('[', ' ').Replace(']', ' ');
                    javajson = javajson.Substring(1, javajson.Length - 2);
                    return javajson;
                }
                else
                {
                    Log4net.Info("字典解析失败 json=" + JSON, "");
                    return null;
                }
            }
            catch (Exception ex)
            {
                Log4net.Error(MethodBase.GetCurrentMethod(), ex, "");
                return null;
            }
        }
        #endregion

        #region  list转DataTable
        /// <summary>
        /// list转DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="varlist"></param>
        /// <returns></returns>
        public static DataTable ToDataTable<T>(this IEnumerable<T> varlist)
        {
            try
            {
                DataTable dtReturn = new DataTable();
                PropertyInfo[] oProps = null;
                foreach (T rec in varlist)
                {
                    if (oProps == null)
                    {
                        oProps = ((Type)rec.GetType()).GetProperties();
                        foreach (PropertyInfo pi in oProps)
                        {
                            Type colType = pi.PropertyType; if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                            {
                                colType = colType.GetGenericArguments()[0];
                            }
                            dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
                        }
                    }
                    DataRow dr = dtReturn.NewRow(); foreach (PropertyInfo pi in oProps)
                    {
                        dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null);
                    }
                    dtReturn.Rows.Add(dr);
                }
                if (dtReturn == null && dtReturn.Rows.Count <= 0)
                {
                    return null;
                }
                return (dtReturn);
            }
            catch (Exception ex)
            {
                Log4net.Error(MethodBase.GetCurrentMethod(), ex, "");
                return null;
            }
        }
        #endregion


        #region 读取EXCEL数据到DataTable
        private static IWorkbook GetWorkbook(string path)
        {
            if (!File.Exists(path))
            {
                return null;
            }
            IWorkbook workbook = null;
            // 采用只读形式获取Excel文件里的数据
            using (var stream = new FileStream(path, FileMode.Open))
            {
                try
                {
                    workbook = new XSSFWorkbook(stream);
                }
                catch
                {
                    workbook = new HSSFWorkbook(stream);
                }
            }
            return workbook;
        }
        public static DataTable GetFirstDataTableFromExcel(string fileName)
        {
            try
            {

                var workbook = GetWorkbook(fileName);
                if (workbook == null)
                {
                    return null;
                }

                var sheet = workbook.GetSheetAt(workbook.FirstVisibleTab);
                if (sheet == null)
                {
                    return null;
                }

                var firstRowIndex = sheet.FirstRowNum;
                var lastRowIndex = sheet.LastRowNum;

                var headRow = sheet.GetRow(firstRowIndex);
                if (headRow == null)
                {
                    throw new Exception(string.Format("文件{0}第一行没有数据，请确认标题行位于第一行。", fileName));
                }

                var firstRow = firstRowIndex != lastRowIndex ? sheet.GetRow(firstRowIndex + 1) : headRow;
                var table = new DataTable(sheet.SheetName);

                for (var i = headRow.FirstCellNum; i < headRow.LastCellNum; i++)
                {
                    var cell = headRow.GetCell(i);
                    var firstRowCell = firstRow.GetCell(i);
                    if (cell == null)
                    {
                        continue;
                    }

                    string columnText;
                    Type columnType;

                    // 若标题行、第一行对应的cell类型均为BLANK、ERROR、Unknown，则表示不存在该列
                    if (cell.CellType == CellType.Blank || cell.CellType == CellType.Error || cell.CellType == CellType.Unknown)
                    {
                        if (firstRowCell != null && (firstRowCell.CellType == CellType.Blank || firstRowCell.CellType == CellType.Error || firstRowCell.CellType == CellType.Unknown))
                        {
                            continue;
                        }
                    }

                    if (cell.CellType == CellType.String)
                    {
                        columnText = cell.StringCellValue.Trim();
                    }
                    else if (cell.CellType == CellType.Numeric)
                    {
                        columnText = cell.NumericCellValue.ToString();
                    }
                    else if (cell.CellType == CellType.Boolean)
                    {
                        columnText = cell.BooleanCellValue.ToString();
                    }
                    else if (cell.CellType == CellType.Formula)
                    {
                        try
                        {
                            columnText = cell.NumericCellValue.ToString();
                            columnType = typeof(decimal);
                        }
                        catch
                        {
                            try
                            {
                                columnText = cell.StringCellValue.Trim();
                                columnType = typeof(string);
                            }
                            catch
                            {
                                columnText = "";
                                columnType = typeof(string);
                            }
                        }
                    }
                    else
                    {
                        columnText = "";
                    }

                    if (firstRowCell == null)
                    {
                        columnType = typeof(string);
                    }
                    else if (firstRowCell.CellType == CellType.String)
                    {
                        columnType = typeof(string);
                    }
                    else if (firstRowCell.CellType == CellType.Numeric)
                    {
                        if (DateUtil.IsCellDateFormatted(firstRowCell))
                        {
                            columnType = typeof(DateTime);
                        }
                        else
                        {
                            columnType = typeof(decimal);
                        }
                    }
                    else if (firstRowCell.CellType == CellType.Boolean)
                    {
                        columnType = typeof(bool);
                    }
                    else if (firstRowCell.CellType == CellType.Formula)
                    {
                        try
                        {
                            columnType = typeof(decimal);
                        }
                        catch
                        {
                            try
                            {
                                columnType = typeof(string);
                            }
                            catch
                            {
                                columnType = typeof(string);
                            }
                        }
                    }
                    else
                    {
                        columnType = typeof(string);
                    }

                    if (table.Columns.Contains(columnText))
                    {
                        columnText = string.Concat(columnText, "1");
                    }
                    var dataColumn = new DataColumn(columnText, columnType);
                    table.Columns.Add(dataColumn);
                }

                for (var i = firstRowIndex + 1; i <= lastRowIndex; i++)
                {
                    var row = sheet.GetRow(i);
                    if (row == null)
                    {
                        continue;
                    }
                    var dataRow = table.NewRow();

                    for (var j = row.FirstCellNum; j < row.LastCellNum; j++)
                    {
                        var cell = row.GetCell(j);
                        if (cell != null)
                        {
                            if (cell.CellType == CellType.String)
                            {
                                dataRow[j] = cell.StringCellValue.Trim();
                            }
                            else if (cell.CellType == CellType.Numeric)
                            {
                                if (DateUtil.IsCellDateFormatted(cell))
                                {
                                    dataRow[j] = cell.DateCellValue;
                                }
                                else
                                {
                                    dataRow[j] = cell.NumericCellValue;
                                }
                            }
                            else if (cell.CellType == CellType.Boolean)
                            {
                                dataRow[j] = cell.BooleanCellValue;
                            }
                            else if (cell.CellType == CellType.Formula)
                            {
                                try
                                {
                                    dataRow[j] = cell.NumericCellValue;
                                }
                                catch
                                {
                                    try
                                    {
                                        dataRow[j] = cell.StringCellValue.Trim();
                                    }
                                    catch
                                    {
                                        dataRow[j] = "";
                                    }
                                }
                            }
                        }
                    }

                    table.Rows.Add(dataRow);
                }
                return table;
            }
            catch (Exception ex)
            {
                Log4net.Error(MethodBase.GetCurrentMethod(), ex);
                return null;
            }
        }
        #endregion

    }
}
